
Norm.Sql = {
	
	deleteRowByIdAndTable : function(tableName) {
		return ["DELETE FROM ", tableName, " WHERE rowid = ?"].join("");
	},
	
	dropTableByName : function(tableName) {
		return "DROP TABLE IF EXISTS " + tableName;
	},
	
	createTableByModelClass : function(modelClass) {
		
		var options = modelClass.options;
		var fields = options.fields;
		var query = ["CREATE TABLE IF NOT EXISTS  ", options.name, "("];
		for (var fieldName in fields) {
			var field = fields[fieldName];
			
			if (! field.isBackwardRelation) {						// ignore ManyToOne and ManyToMany, as they are backward relations
				if (! field.isRelation) {
					query.push(fieldName);
					query.push(" ");
				}
				query.push(field.toSql());
				query.push(",");
			}
		}
		query = query.splice(0, query.length - 1);
		query.push(")");
		return query.join("");
	},
	
	deleteFromTableWhere : function(tableName, whereExpression) {
		return ["DELETE FROM ", tableName, whereExpression ? " WHERE " : "", whereExpression].join("");	
	},
	
	updateByModel : function(modelClass) {
		return ["UPDATE ", modelClass.options.name, " SET ", Norm.Sql._fieldsValue(modelClass), " WHERE rowid = ?"].join("");
	},
	
	selectCount : function(tableName, whereExpression) {
		return ["SELECT COUNT(*) as c FROM ", tableName, whereExpression ? " WHERE " + whereExpression :""].join("");
	},
	
	selectWithManyToMany : function(modelClass, relatedClass, m2mTableName, whereExpression, rowid) {
		
		var query = ["SELECT DISTINCT ", relatedClass.options.name, ".", "rowid,"];
		query.push(Norm.Sql._selectFields(relatedClass));
		query.push(" FROM ");
		query.push(relatedClass.options.name);
		query.push(Norm.Sql._selectJoins(relatedClass));
		query.push(Norm.Sql._m2mJoins(modelClass.options.name, relatedClass.options.name, m2mTableName, rowid));
		if (whereExpression) {
			query.push(" WHERE ");
			query.push(whereExpression);
		}
		return query.join("");
	},
	
	selectWithForeignKeys : function(modelClass, whereExpression) {
		
		var query = ["SELECT ", modelClass.options.name, ".", "rowid,"];
		query.push(Norm.Sql._selectFields(modelClass));
		query.push(" FROM ");
		query.push(modelClass.options.name);
		query.push(Norm.Sql._selectJoins(modelClass));
		if (whereExpression) {
			query.push(" WHERE ");
			query.push(whereExpression);
		}
		return query.join("");
	},
	
	insertRowByModel : function(modelClass) {
		
		var self = Norm.Sql;
		return ["INSERT INTO ", modelClass.options.name, " (", self._fieldsByModel(modelClass), ") VALUES (", self._valuesByModel(modelClass), ")"].join("");
	},
	
	oneToManySql : function(relatedModelName, allowNull) {
		return [relatedModelName,"_id INTEGER", ! allowNull ? " NOT NULL " : " " , "CONSTRAINT fk_", relatedModelName, "_id REFERENCES ", relatedModelName, "(rowid)"].join("");
	},
	
	createInsertForeignKeyTrigger : function(tableName, relatedTableName, allowNull) {
		return Norm.Sql._createInsertUpdateForeignKeyTrigger(tableName, relatedTableName, false, allowNull);
	},
	
	createUpdateForeignKeyTrigger : function(tableName, relatedTableName, allowNull) {
		return Norm.Sql._createInsertUpdateForeignKeyTrigger(tableName, relatedTableName, true, allowNull);
	},
	
	createDeleteRestrictTrigger : function(tableName, relatedTableName) {
		return Norm.Sql._createDeleteCascadeOrRestrictTrigger(tableName, relatedTableName, false);
	},
	
	createDeleteCascadeTrigger : function(tableName, relatedTableName) {
		return Norm.Sql._createDeleteCascadeOrRestrictTrigger(tableName, relatedTableName, true);
	},
	
	dropForeignKeyInsertTrigger : function(tableName, relatedTableName) {
		return Norm.Sql._dropForeignKeyTrigger(tableName, relatedTableName, "i");
	},
	
	dropForeignKeyUpdateTrigger : function(tableName, relatedTableName) {
		return Norm.Sql._dropForeignKeyTrigger(tableName, relatedTableName, "u");
	},
	
	dropForeignKeyDeleteTrigger : function(tableName, relatedTableName) {
		return Norm.Sql._dropForeignKeyTrigger(tableName, relatedTableName, "d");
	},
	
	_createDeleteCascadeOrRestrictTrigger : function(tableName, relatedTableName, cascade) {
		
		  var sql = ["CREATE TRIGGER IF NOT EXISTS fkd_", tableName, "_", relatedTableName, "_rowid ",
		  			"BEFORE DELETE ON ", relatedTableName, " FOR EACH ROW BEGIN"];
		  
		  if (cascade) {
		  	sql.push(" DELETE from ");
			sql.push(tableName);
			sql.push(" WHERE ");
			sql.push(relatedTableName);
			sql.push("_id = OLD.rowid;");		
		  } else {
		  	sql.push(" SELECT RAISE(ROLLBACK, 'delete on table \"");
			sql.push(relatedTableName);
			sql.push("\" violates foreign key constraint \"fk_");
			sql.push(tableName);
			sql.push("_");
			sql.push(relatedTableName);
			sql.push("_id\"')");
			sql.push("WHERE (SELECT ");
			sql.push(relatedTableName);
			sql.push("_id FROM ");
			sql.push(tableName);
			sql.push(" WHERE ");
			sql.push(relatedTableName);
			sql.push("_id = OLD.rowid) IS NOT NULL;");
		  }
		  sql.push(" END;");
		  return sql.join("");
	},
	
	_createInsertUpdateForeignKeyTrigger : function(tableName, relatedTableName, update, allowNull) {
		
		var sql =  ["CREATE TRIGGER IF NOT EXISTS fk", update ? "u" : "i", "_", tableName, "_", relatedTableName, "_rowid BEFORE "];
		if (update) {
			sql.push("UPDATE");
		} else {
			sql.push("INSERT");
		}			
		sql.push(" ON ");
		sql.push(tableName);
		sql.push(" FOR EACH ROW BEGIN ");
		sql.push("SELECT RAISE(ROLLBACK, '");
		if (update) {
			sql.push("update");
		} else {
			sql.push("insert");
		}
		sql.push(" on table \"");
		sql.push(tableName);
		sql.push("\" violates foreign key constraint \"fk_");
		sql.push(relatedTableName);
		sql.push("_rowid");
		sql.push("\"') WHERE ");
		if (allowNull) {
			sql.push("NEW.");
			sql.push(relatedTableName);
			sql.push("_id IS NOT NULL AND ");
		}
		sql.push("(SELECT rowid FROM ");
		sql.push(relatedTableName);
		sql.push(" WHERE rowid = NEW.");
		sql.push(relatedTableName);
		sql.push("_id) IS NULL; END;");
		return sql.join("");
	},
	
	_selectFields : function(modelClass) {
		
		var fields = modelClass.options.fields;
		var modelName = modelClass.options.name;
		var sql = [];
		for (var fieldName in fields) {
			var field = fields[fieldName];
			if (! field.isBackwardRelation) {
				if (field.isRelation) {
					var relatedClass = field.getRelatedClass();
					if (relatedClass == modelClass) {
						sql.push(modelName);
						sql.push(".");
						sql.push(relatedClass.options.name);
						sql.push("_id");
					} else {
						sql.push(Norm.Sql._selectFields(relatedClass));
					}
				} else {
					sql.push(modelName);
					sql.push(".");
					sql.push(fieldName);
					sql.push(" AS ");
					sql.push(modelName);
					sql.push("_");
					sql.push(fieldName);
				}
				sql.push(",");
			}
		}
		return sql.splice(0,sql.length-1).join("");
	},
	
	_selectJoins : function(modelClass) {
		
		var fields = modelClass.options.fields;
		var name = modelClass.options.name;
		var sql = [];
		for (var fieldName in fields) {
			var field = fields[fieldName];
			if (field.isRelation && ! field.isBackwardRelation && field.getRelatedClass() != modelClass) {
				var relatedName = field.getRelatedClass().options.name;
				sql.push(" LEFT JOIN ");
				sql.push(relatedName);
				sql.push(" ON ");
				sql.push(relatedName);
				sql.push(".rowid = ");
				sql.push(name);
				sql.push(".");
				sql.push(relatedName);
				sql.push("_id ");
			}
		}
		return sql.join("");
	},
	
	_m2mJoins : function(modelName, relatedName, m2mTableName, rowid) {
		return [" JOIN ", m2mTableName, " ON ", m2mTableName, ".", relatedName, "_id = ", relatedName, ".rowid AND ", m2mTableName, ".", modelName, "_id =", rowid].join("");
	},
	
	_fieldsByModel : function(modelClass) {
		
		var fields = [];
		for (var fieldName in modelClass.options.fields) {
			var field = modelClass.options.fields[fieldName];
			if (! field.isBackwardRelation) {
				if (! field.isRelation) {
					fields.push(fieldName);
				} else {
					fields.push(field.getRelatedClass().options.name + "_id");
				}
			}
		}
		return fields.join(",");
	},
	
	_valuesByModel : function(modelClass) {
		
		var values = [];
		var fields = modelClass.options.fields;
		for (var fieldName in fields) {
			if (! fields[fieldName].isBackwardRelation){
				values.push("?");
			}
		}
		return values.join(",");
	},
	
	_fieldsValue : function(modelClass) {
		
		var fields = modelClass.options.fields;
		var sql = [];
		for (var fieldName in fields) {
			var field = fields[fieldName];
			if (! field.isBackwardRelation) {
				if (! field.isRelation) {
					sql.push(fieldName + "=?");
				} else {
					sql.push(field.getRelatedClass().options.name + "_id" + "=?");
				}
			}
		}
		return sql.join(",");
	},
	
	_dropForeignKeyTrigger : function(tableName, relatedTableName, type) {
		return ["DROP TRIGGER IF EXISTS ", "fk", type, "_", tableName, "_", relatedTableName, "_rowid"].join("");
	}
	
};
